﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using Npgsql;
using System.Data;


namespace EstevesRecordersProject
{
    public class Repository
    {
        /*
         * 
         * Connection
         * 
         */

        private const String CONNECTIONSTRINGNAME = "Recorders";

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        private static NpgsqlConnection GetPostgreSQLDatabaseConnection(string name)
        {

            NpgsqlConnection MyConnection = new NpgsqlConnection();
            MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings[name].ConnectionString;
            MyConnection.Open();

            return MyConnection;
        }

        /*
         *
         * Employee
         * 
         */

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static List<Employee> GetAllEmployees()
        {
            string query = "SELECT numer, imie, nazwisko FROM pracownicy";

            using (NpgsqlConnection conn = GetPostgreSQLDatabaseConnection(CONNECTIONSTRINGNAME))
            {
                NpgsqlCommand cmd = new NpgsqlCommand(query, conn);
                NpgsqlDataReader reader = cmd.ExecuteReader();
                List<Employee> employeesList = new List<Employee>();

                while (reader.Read())
                {
                    int number = Int32.Parse(reader["numer"].ToString());
                    String firstName = reader["imie"].ToString();
                    String lastName = reader["nazwisko"].ToString();

                    Employee e = new Employee();

                    e.number = number;
                    e.firstName = firstName;
                    e.lastName = lastName;

                    employeesList.Add(e);
                }

                return employeesList;
            }
        }

        public static Employee GetEmployeeById(int idEmployee)
        {
            string query = "SELECT  numer, imie, nazwisko FROM pracownicy WHERE numer = @NR ";

            using (NpgsqlConnection conn = GetPostgreSQLDatabaseConnection(CONNECTIONSTRINGNAME))
            {
                NpgsqlCommand cmd = new NpgsqlCommand(query, conn);

                NpgsqlParameter param = new NpgsqlParameter();
                param.Value = idEmployee;
                param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bigint;
                param.ParameterName = "@NR";
                cmd.Parameters.Add(param);

                NpgsqlDataReader reader = cmd.ExecuteReader();

                Employee e = new Employee();

                while (reader.Read())
                {
                    int number = Int32.Parse(reader["numer"].ToString());
                    String firstName = reader["imie"].ToString();
                    String lastName = reader["nazwisko"].ToString();

                    e.number = number;
                    e.firstName = firstName;
                    e.lastName = lastName;
                }

                return e;
            }
        }

        /*
         * 
         * RECORDER
         * 
         */

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="name2"></param>
        /// <returns></returns>
        public static int AddNewRecorder( String name, String name2, int idEmployee )
        {
            string query = "";
            string query2 = "";

            query +=
            "INSERT INTO \"Recorders\".\"Recorder\"( " +
            "name, name2, \"idEmployee\") " +
            "VALUES ( @NAME, @NAME2, @IDEMPLOYEE); ";

            query2 +=
            "SELECT MAX(id) FROM \"Recorders\".\"Recorder\"";


            using (NpgsqlConnection conn = GetPostgreSQLDatabaseConnection(CONNECTIONSTRINGNAME))
            {
                NpgsqlCommand cmd = new NpgsqlCommand(query, conn);

                NpgsqlParameter param = new NpgsqlParameter();
                param.Value = name;
                param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
                param.ParameterName = "@NAME";
                cmd.Parameters.Add(param);

                NpgsqlParameter param2 = new NpgsqlParameter();
                param2.Value = name2;
                param2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
                param2.ParameterName = "@NAME2";
                cmd.Parameters.Add(param2);

                NpgsqlParameter param3 = new NpgsqlParameter();
                param3.Value = idEmployee;
                param3.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bigint;
                param3.ParameterName = "@IDEMPLOYEE";
                cmd.Parameters.Add(param3);

                cmd.ExecuteNonQuery();

                NpgsqlCommand cmd2 = new NpgsqlCommand(query2, conn);
                int id = Int32.Parse((cmd2.ExecuteScalar()).ToString());

                return id;

           }
        }     

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        public static void DeleteRecorder(int id)
        {

            string query = "";

            query +=
            "DELETE FROM \"Recorders\".\"Recorder\" " +
            "WHERE  id = " + id.ToString() + " ";

            using (NpgsqlConnection conn = GetPostgreSQLDatabaseConnection(CONNECTIONSTRINGNAME))
            {
                NpgsqlCommand cmd = new NpgsqlCommand(query, conn);
                cmd.ExecuteNonQuery();
            }

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="recorder"></param>
        public static void EditRecorder(Recorder recorder)
        {
            String query = "";
            query +=
            "UPDATE \"Recorders\".\"Recorder\" " +
            "SET  \"name\"= @NAME, name2= @NAME2 " +
            "WHERE id= @ID ";


            using (NpgsqlConnection conn = GetPostgreSQLDatabaseConnection(CONNECTIONSTRINGNAME))
            {
                NpgsqlCommand cmd = new NpgsqlCommand(query, conn);

                NpgsqlParameter param = new NpgsqlParameter();
                param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
                param.NpgsqlValue = recorder.id;
                param.ParameterName = "@ID";
                cmd.Parameters.Add(param);

                NpgsqlParameter param2 = new NpgsqlParameter();
                param2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
                param2.NpgsqlValue = recorder.name;
                param2.ParameterName = "@NAME";
                cmd.Parameters.Add(param2);

                NpgsqlParameter param3 = new NpgsqlParameter();
                param3.ParameterName = "@NAME2";
                param3.NpgsqlValue = recorder.name2;
                param3.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
                cmd.Parameters.Add(param3);

                cmd.ExecuteNonQuery();

            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static List<Recorder> GetAllRecorders()
        {
            string query = "";

            query +=
            "SELECT id, \"name\", name2 " +
            "FROM \"Recorders\".\"Recorder\"  ";

            using (NpgsqlConnection conn = GetPostgreSQLDatabaseConnection(CONNECTIONSTRINGNAME))
            {
                NpgsqlCommand cmd = new NpgsqlCommand(query, conn);
                NpgsqlDataReader reader = cmd.ExecuteReader();
                List<Recorder> recorderList = new List<Recorder>();

                while(reader.Read())
                {
                    int id = Int32.Parse( reader["id"].ToString() );
                    String name = reader["name"].ToString();
                    String name2 = reader["name2"].ToString();

                    Recorder r = new Recorder();

                    r.id = id;
                    r.name = name;
                    r.name2 = name2;

                    recorderList.Add(r);
                }

                return recorderList;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="idRecorder"></param>
        /// <returns></returns>
        public static Recorder GetRecorderById(int idRecorder)
        {
            string query = "";

            query +=
            "SELECT id, \"name\", name2 " +
            "FROM \"Recorders\".\"Recorder\" WHERE id =  @ID ";

            using (NpgsqlConnection conn = GetPostgreSQLDatabaseConnection(CONNECTIONSTRINGNAME))
            {
                NpgsqlCommand cmd = new NpgsqlCommand(query, conn);

                NpgsqlParameter param = new NpgsqlParameter();
                param.Value = idRecorder;
                param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
                param.ParameterName = "@ID";
                cmd.Parameters.Add(param);
                
                NpgsqlDataReader reader = cmd.ExecuteReader();

                Recorder r = new Recorder();

                while (reader.Read())
                {
                    int id = Int32.Parse(reader["id"].ToString());
                    String name = reader["name"].ToString();
                    String name2 = reader["name2"].ToString();
                    
                    r.id = id;
                    r.name = name;
                    r.name2 = name2;
                }

                return r;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="idRecorder"></param>
        /// <returns></returns>
        public static int GetNrOfLines(int idRecorder)
        {
            string query = "";

            query +=
            "SELECT COUNT(*) " +
            "FROM \"Recorders\".\"Line\"  " +
            "WHERE \"idRecorder\" = @IDRECORDER ";

            using (NpgsqlConnection conn = GetPostgreSQLDatabaseConnection(CONNECTIONSTRINGNAME))
            {
                NpgsqlCommand cmd = new NpgsqlCommand(query, conn);

                NpgsqlParameter param = new NpgsqlParameter();
                param.Value = idRecorder;
                param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
                param.ParameterName = "@IDRECORDER";
                cmd.Parameters.Add(param);

                int id = Int32.Parse((cmd.ExecuteScalar()).ToString());

                return id;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="recorder"></param>
        /// <param name="from"></param>
        /// <param name="to"></param>
        /// <returns></returns>
        public static List<DateTime> GetLoadedDays(Recorder recorder, DateTime from, DateTime to)
        {
            List<DateTime> days = new List<DateTime>();
            List<int> lineIdList = new List<int>();

            foreach (Line l in recorder.Lines)
            {
                lineIdList.Add(l.id);
            }

            String query = "SELECT DISTINCT date_trunc('day', date) FROM \"Recorders\".\"Performance\" WHERE \"idLine\" = @LINEID and date >= @FROM and date <= @TO  ;";

            using (NpgsqlConnection conn = GetPostgreSQLDatabaseConnection(CONNECTIONSTRINGNAME))
            {
                foreach (int lineId in lineIdList)
                {
                    NpgsqlCommand cmd = new NpgsqlCommand(query, conn);

                    NpgsqlParameter param1 = new NpgsqlParameter();
                    param1.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Timestamp;
                    param1.NpgsqlValue = from;
                    param1.ParameterName = "@FROM";
                    cmd.Parameters.Add(param1);

                    NpgsqlParameter param2 = new NpgsqlParameter();
                    param2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Timestamp;
                    param2.NpgsqlValue = to;
                    param2.ParameterName = "@TO";
                    cmd.Parameters.Add(param2);

                    NpgsqlParameter param3 = new NpgsqlParameter();
                    param3.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
                    param3.NpgsqlValue = lineId;
                    param3.ParameterName = "@LINEID";
                    cmd.Parameters.Add(param3);

                    NpgsqlDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        DateTime date = DateTime.Parse(reader[0].ToString());

                        if (!days.Contains(date)) days.Add(date);
                    }
                }
            }

            return days;              
        }

        /*
         * 
         * LINE
         * 
         */
        /// <summary>
        /// 
        /// </summary>
        /// <param name="idRecorder"></param>
        /// <param name="name"></param>
        /// <param name="nr"></param>
        public static void AddNewLine(int? idRecorder, String name, int? nr)
        {
            string query = "";

            query +=
            "INSERT INTO \"Recorders\".\"Line\"( " +
            " \"idRecorder\", \"name\", nr)  " +
            "VALUES (@IDRECORDER, @NAME, @NR); ";

            using (NpgsqlConnection conn = GetPostgreSQLDatabaseConnection(CONNECTIONSTRINGNAME))
            {
                NpgsqlCommand cmd = new NpgsqlCommand(query, conn);

                NpgsqlParameter param = new NpgsqlParameter();
                param.Value = idRecorder;
                param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
                param.ParameterName = "@IDRECORDER";
                cmd.Parameters.Add(param);

                NpgsqlParameter param2 = new NpgsqlParameter();
                param2.Value = name;
                param2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
                param2.ParameterName = "@NAME";
                cmd.Parameters.Add(param2);

                NpgsqlParameter param3 = new NpgsqlParameter();
                param3.Value = nr;
                param3.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
                param3.ParameterName = "@NR";
                cmd.Parameters.Add(param3);

                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="line"></param>
        public static void EditLine(Line line)
        {
            String query = "";
            query +=
            "UPDATE \"Recorders\".\"Line\" " +
            "SET  \"name\"= @NAME " +
            "WHERE id= @ID ";


            using (NpgsqlConnection conn = GetPostgreSQLDatabaseConnection(CONNECTIONSTRINGNAME))
            {
                NpgsqlCommand cmd = new NpgsqlCommand(query, conn);

                NpgsqlParameter param = new NpgsqlParameter();
                param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
                param.NpgsqlValue = line.id;
                param.ParameterName = "@ID";
                cmd.Parameters.Add(param);

                NpgsqlParameter param2 = new NpgsqlParameter();
                param2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
                param2.NpgsqlValue = line.name;
                param2.ParameterName = "@NAME";
                cmd.Parameters.Add(param2);

                cmd.ExecuteNonQuery();

            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="idRecorder"></param>
        /// <param name="nr"></param>
        public static void DeleteLine(int idRecorder, int nr)
        {
            String query = "";

            query +=
                " DELETE FROM \"Recorders\".\"Line\" WHERE \"idRecorder\" = " + idRecorder.ToString() + " and nr = " + nr.ToString() + " ";

            using (NpgsqlConnection conn = GetPostgreSQLDatabaseConnection(CONNECTIONSTRINGNAME))
            {
                NpgsqlCommand cmd = new NpgsqlCommand(query, conn);
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="idRecorder"></param>
        /// <param name="nr"></param>
        /// <returns></returns>
        public static Line GetLine(int idRecorder, int nr)
        {
            String query = "";
            query +=
            "SELECT * " +
            "FROM \"Recorders\".\"Line\"  " +
            "WHERE \"idRecorder\" = @IDRECORDER and nr = @NR ";

            using (NpgsqlConnection conn = GetPostgreSQLDatabaseConnection(CONNECTIONSTRINGNAME))
            {
                NpgsqlCommand cmd = new NpgsqlCommand(query, conn);

                NpgsqlParameter param = new NpgsqlParameter();
                param.Value = idRecorder;
                param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
                param.ParameterName = "@IDRECORDER";
                cmd.Parameters.Add(param);

                NpgsqlParameter param2 = new NpgsqlParameter();
                param2.Value = nr;
                param2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
                param2.ParameterName = "@NR";
                cmd.Parameters.Add(param2);

                NpgsqlDataReader reader = cmd.ExecuteReader();

                Line l = new Line();

                if (reader.Read())
                {
                    int id = Int32.Parse(reader["id"].ToString());
                    String name = reader["name"].ToString();
                    String lineNr = reader["nr"].ToString();

                    l.id = id;
                    l.name = name;
                    l.nr = lineNr;
                }
                else
                {
                    throw new WrongLineNrException();
                }

                return l;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="idLine"></param>
        /// <returns></returns>
        public static Line GetLine(int idLine)
        {
            String query = "";
            query +=
            "SELECT * " +
            "FROM \"Recorders\".\"Line\"  " +
            "WHERE id = @IDLINE ";

            using (NpgsqlConnection conn = GetPostgreSQLDatabaseConnection(CONNECTIONSTRINGNAME))
            {
                NpgsqlCommand cmd = new NpgsqlCommand(query, conn);

                NpgsqlParameter param = new NpgsqlParameter();
                param.Value = idLine;
                param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
                param.ParameterName = "@IDLINE";
                cmd.Parameters.Add(param);

                NpgsqlDataReader reader = cmd.ExecuteReader();

                Line l = new Line();

                if (reader.Read())
                {
                    int id = Int32.Parse(reader["id"].ToString());
                    String name = reader["name"].ToString();
                    String lineNr = reader["nr"].ToString();

                    l.id = id;
                    l.name = name;
                    l.nr = lineNr;
                }
                else
                {
                    throw new WrongLineNrException();
                }

                return l;
            }
        }
        /*
         * 
         * PERFORMANCE
         * 
         */

        /// <summary>
        /// 
        /// </summary>
        /// <param name="lineId"></param>
        /// <param name="date"></param>
        /// <returns></returns>
        public static int CheckDate(int lineId, DateTime date)
        {
            String query = "";

            query = 
                "SELECT count(*) " +
                "FROM \"Recorders\".\"Performance\" " +
                "WHERE \"idLine\" = @IDLINE and date = @DATE ";

            using (NpgsqlConnection conn = GetPostgreSQLDatabaseConnection(CONNECTIONSTRINGNAME))
            {
                NpgsqlCommand cmd = new NpgsqlCommand(query, conn);

                NpgsqlParameter param = new NpgsqlParameter();
                param.Value = lineId;
                param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
                param.ParameterName = "@IDLINE";
                cmd.Parameters.Add(param);

                NpgsqlParameter param2 = new NpgsqlParameter();
                param2.Value = date;
                param2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Timestamp;
                param2.ParameterName = "@DATE";
                cmd.Parameters.Add(param2);

                int count = Int32.Parse((cmd.ExecuteScalar()).ToString());

                return count;
            }

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="performanceList"></param>
        public static void AddNewPerformanceList(List<Performance> performanceList)
        {
            String query = "";
            query +=
            "INSERT INTO \"Recorders\".\"Performance\"( " +
            "\"idLine\", date, \"value\") " +
            "VALUES (@IDLINE, @DATE, @VALUE); ";

            using (NpgsqlConnection con = GetPostgreSQLDatabaseConnection(CONNECTIONSTRINGNAME))
            {

                foreach (Performance p in performanceList )
                {
                    NpgsqlCommand cmd = new NpgsqlCommand(query, con);

                    NpgsqlParameter param1 = new NpgsqlParameter();
                    param1.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
                    param1.ParameterName = "@IDLINE";
                    param1.Value = p.idLinel;
                    cmd.Parameters.Add(param1);

                    NpgsqlParameter param2 = new NpgsqlParameter();
                    param2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Timestamp;
                    param2.ParameterName = "@DATE";
                    param2.Value = p.date;
                    cmd.Parameters.Add(param2);

                    NpgsqlParameter param3 = new NpgsqlParameter();
                    param3.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
                    param3.ParameterName = "@VALUE";
                    param3.Value = p.value;
                    cmd.Parameters.Add(param3);

                    cmd.ExecuteNonQuery();
                }
            }

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="performance"></param>
        public static void AddNewPerformance(Performance performance)
        {
            String query = "";
            query +=
            "INSERT INTO \"Recorders\".\"Performance\"( " +
            "\"idLine\", date, \"value\") " +
            "VALUES (@IDLINE, @DATE, @VALUE); ";

            using (NpgsqlConnection con = GetPostgreSQLDatabaseConnection(CONNECTIONSTRINGNAME))
            {

                
                NpgsqlCommand cmd = new NpgsqlCommand(query, con);

                NpgsqlParameter param1 = new NpgsqlParameter();
                param1.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
                param1.ParameterName = "@IDLINE";
                param1.Value = performance.idLinel;
                cmd.Parameters.Add(param1);

                NpgsqlParameter param2 = new NpgsqlParameter();
                param2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Timestamp;
                param2.ParameterName = "@DATE";
                param2.Value = performance.date;
                cmd.Parameters.Add(param2);

                NpgsqlParameter param3 = new NpgsqlParameter();
                param3.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
                param3.ParameterName = "@VALUE";
                param3.Value = performance.value;
                cmd.Parameters.Add(param3);

                cmd.ExecuteNonQuery();
                
            }

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="idLine"></param>
        /// <param name="from"></param>
        /// <param name="to"></param>
        /// <returns></returns>
        public static int DeletePerformance(int idLine , DateTime from, DateTime to)
        {
            String query = "DELETE FROM \"Recorders\".\"Performance\" WHERE \"idLine\" = @IDLINE and date >= @FROM  and date <= @TO ";

            using(NpgsqlConnection con = GetPostgreSQLDatabaseConnection(CONNECTIONSTRINGNAME))
            {
                NpgsqlCommand cmd = new NpgsqlCommand(query, con);

                NpgsqlParameter param1 = new NpgsqlParameter();
                param1.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
                param1.ParameterName = "@IDLINE";
                param1.Value = idLine;
                cmd.Parameters.Add(param1);

                NpgsqlParameter param2 = new NpgsqlParameter();
                param2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Timestamp;
                param2.ParameterName = "@FROM";
                param2.Value = from;
                cmd.Parameters.Add(param2);

                NpgsqlParameter param3 = new NpgsqlParameter();
                param3.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Timestamp;
                param3.ParameterName = "@TO";
                param3.Value = to;
                cmd.Parameters.Add(param3);


                int count = cmd.ExecuteNonQuery();

                return count;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="line"></param>
        /// <param name="dateStart"></param>
        /// <param name="dateStop"></param>
        /// <returns></returns>
        public static List<Performance> GetLinePerformanceInTime(Line line, DateTime dateStart, DateTime dateStop)
        {
            String query = "";
            query += " SELECT id, \"idLine\", date, \"value\" FROM \"Recorders\".\"Performance\" " +
                        "WHERE \"idLine\" = @IDLINE and date >= @DATESTART and date <= @DATESTOP ORDER BY date  ";

            using (NpgsqlConnection con = GetPostgreSQLDatabaseConnection(CONNECTIONSTRINGNAME))
            {
                NpgsqlCommand cmd = new NpgsqlCommand(query, con);

                NpgsqlParameter param1 = new NpgsqlParameter();
                param1.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
                param1.ParameterName = "@IDLINE";
                param1.Value = line.id;
                cmd.Parameters.Add(param1);

                NpgsqlParameter param2 = new NpgsqlParameter();
                param2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Timestamp;
                param2.ParameterName = "@DATESTART";
                param2.Value = dateStart;
                cmd.Parameters.Add(param2);

                NpgsqlParameter param3 = new NpgsqlParameter();
                param3.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Timestamp;
                param3.ParameterName = "@DATESTOP";
                param3.Value = dateStop;
                cmd.Parameters.Add(param3);

                NpgsqlDataReader reader = cmd.ExecuteReader();

                List<Performance> performanceList = new List<Performance>();

                while (reader.Read())
                {
                    int id = Int32.Parse(reader["id"].ToString());
                    int idLine = Int32.Parse(reader["idLine"].ToString());
                    int value = Int32.Parse(reader["value"].ToString());
                    DateTime date = DateTime.Parse(reader["date"].ToString());

                    Performance p = new Performance();

                    p.id = id;
                    p.idLinel = idLine;
                    p.value = value;
                    p.date = date;

                    performanceList.Add(p);
                }

                return performanceList;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="recorder"></param>
        /// <param name="dateStart"></param>
        /// <param name="dateStop"></param>
        /// <returns></returns>
        public static List<Performance> GetRecorderPerformanceInTime(Recorder recorder, DateTime dateStart, DateTime dateStop)
        {
            String query = "";
            query += 
                "SELECT \"idRecorder\" , \"idLine\", p.id as \"idPerformance\", \"date\", value " +
                "FROM \"Recorders\".\"Performance\" p " +
                "left join \"Recorders\".\"Line\" l on p.\"idLine\" = l.id " +
                "left join \"Recorders\".\"Recorder\" r on r.id = l.\"idRecorder\" " +
                "where \"idRecorder\" =  @IDRECORDER and date >= @DATESTART and date <= @DATESTOP " +
                "order by date; ";

            using (NpgsqlConnection con = GetPostgreSQLDatabaseConnection(CONNECTIONSTRINGNAME))
            {
                NpgsqlCommand cmd = new NpgsqlCommand(query, con);

                NpgsqlParameter param1 = new NpgsqlParameter();
                param1.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
                param1.ParameterName = "@IDRECORDER";
                param1.Value = recorder.id;
                cmd.Parameters.Add(param1);

                NpgsqlParameter param2 = new NpgsqlParameter();
                param2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Timestamp;
                param2.ParameterName = "@DATESTART";
                param2.Value = dateStart;
                cmd.Parameters.Add(param2);

                NpgsqlParameter param3 = new NpgsqlParameter();
                param3.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Timestamp;
                param3.ParameterName = "@DATESTOP";
                param3.Value = dateStop;
                cmd.Parameters.Add(param3);

                NpgsqlDataReader reader = cmd.ExecuteReader();

                List<Performance> performanceList = new List<Performance>();

                while (reader.Read())
                {
                    int id = Int32.Parse(reader["idPerformance"].ToString());
                    int idLine = Int32.Parse(reader["idLine"].ToString());
                    int value = Int32.Parse(reader["value"].ToString());
                    DateTime date = DateTime.Parse(reader["date"].ToString());

                    Performance p = new Performance();

                    p.id = id;
                    p.idLinel = idLine;
                    p.value = value;
                    p.date = date;

                    performanceList.Add(p);
                }

                return performanceList;
            }
        }
        
    }
}
